﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BaseDal
{
    public class UploadDal
    {
        //表名
        private const string tableName = "FileData";
        static readonly Lazy<UploadDal> dal = new Lazy<UploadDal>(() => new UploadDal());
        public static UploadDal Instance { get; } = dal.Value;
        private UploadDal() { }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="relationId"></param>
        /// <param name="relationTable"></param>
        /// <returns></returns>
        public DataTable GetFiles(decimal relationId, string relationTable)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,RelationId,FileName,FileType,");
            strSql.Append("FilePath,ThumbnailPath,DeletePath,DeleteType,FileStatus,FileSize,IsDelete,RelationTable ");
            strSql.Append(" FROM " + tableName);
            strSql.Append(" where IsDelete=0 and RelationId=" + relationId + " and RelationTable='" + relationTable + "'");
            strSql.Append(" order by ID");
            DataTable dt = SqlHelper.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 根据List relationIds查询所有文件
        /// </summary>
        /// <param name="relationIds"></param>
        /// <param name="relationTable"></param>
        /// <returns></returns>
        public DataTable GetFilesForItems(List<decimal> relationIds, string relationTable)
        {
            if (relationIds == null || relationIds.Count == 0)
            {
                throw new ArgumentException("relationIds cannot be null or empty", nameof(relationIds));
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ID, RelationId, FileName, FileType, ");
            strSql.Append("FilePath, ThumbnailPath, DeletePath, DeleteType, FileStatus, FileSize, IsDelete, RelationTable ");
            strSql.Append("FROM " + tableName + " ");
            strSql.Append("WHERE IsDelete = 0 AND RelationTable = @RelationTable ");
            strSql.Append("AND RelationId IN (");

            var parameters = new List<SqlParameter>
                {
                    new SqlParameter("@RelationTable", relationTable)
                };

            for (int i = 0; i < relationIds.Count; i++)
            {
                var parameterName = "@RelationId" + i;
                strSql.Append(parameterName);
                if (i < relationIds.Count - 1)
                {
                    strSql.Append(", ");
                }
                parameters.Add(new SqlParameter(parameterName, relationIds[i]));
            }
            strSql.Append(") ORDER BY ID");

            DataTable dt = SqlHelper.Query(strSql.ToString(), parameters.ToArray()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 通过文件ID查询单个文件
        /// </summary>
        /// <param name="fileID"></param>
        /// <returns></returns>
        public FileData GetFileByID(string fileID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,RelationId,FileName,FileType,");
            strSql.Append("FilePath,ThumbnailPath,DeletePath,DeleteType,FileStatus,FileSize,IsDelete,RelationTable ");
            strSql.Append(" FROM " + tableName);
            strSql.Append(" where ID=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id",fileID)
            };
            DataSet ds = SqlHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return CommonOperation.DataTableToModel<FileData>(ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddFile(FileData model)
        {
            model.ID = Utils.GetNextID();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into " + tableName + " (");
            strSql.Append("ID,FileName,FileType,FilePath,ThumbnailPath,DeletePath,DeleteType,FileStatus,");
            strSql.Append("FileSize,IsDelete,RelationTable )");
            strSql.Append(" values (");
            strSql.Append("@id,@FileName,@FileType,@FilePath,@ThumbnailPath,@DeletePath,@DeleteType,@FileStatus,@FileSize,@IsDelete,@RelationTable)");
            //strSql.Append("SELECT  @@IDENTITY  as  'id'");

            SqlParameter[] parameters = {
                    new SqlParameter("@id",model.ID),
                    new SqlParameter("@FileName", model.FileName),
                    new SqlParameter("@FileType", model.FileType),
                    new SqlParameter("@FilePath", model.FilePath),
                    new SqlParameter("@ThumbnailPath", model.ThumbnailPath),
                    new SqlParameter("@DeletePath", model.DeletePath),
                    new SqlParameter("@DeleteType", model.DeleteType),
                    new SqlParameter("@FileStatus", model.FileStatus),
                    new SqlParameter("@FileSize", model.FileSize),
                    new SqlParameter("@IsDelete", model.IsDelete),
                    new SqlParameter("@RelationTable",model.RelationTable)
            };

            int result = SqlHelper.ExecuteSql(strSql.ToString(), parameters);

            return result > 0;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="RelationId"></param>
        /// <param name="IsDelete"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool UpdateFile(decimal RelationId, int IsDelete, string ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableName + " set ");
            strSql.Append("RelationId=@RelationId,");
            strSql.Append("IsDelete=@isDelete ");
            strSql.Append(" where ID in(" + ids + ")");
            SqlParameter[] parameters = {
                    new SqlParameter("@RelationId", RelationId),
                    new SqlParameter("@isDelete",IsDelete)
            };

            int rows = SqlHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 通过文件ID删除文件
        /// </summary>
        /// <param name="fileID"></param>
        /// <returns></returns>
        public bool DeleteFilesByID(string fileID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + tableName);
            strSql.Append(" where ID = @id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", fileID)
            };

            int rows = SqlHelper.ExecuteSql(strSql.ToString(), parameters);
            return rows > 0;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="relationIds"></param>
        /// <param name="relationTable"></param>
        /// <returns></returns>
        public bool DeleteFiles(string relationIds, string relationTable)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + tableName);
            strSql.Append(" where RelationId in(" + relationIds + ") ");
            strSql.Append(" and RelationTable ='" + relationTable + "'");

            int rows = SqlHelper.ExecuteSql(strSql.ToString());
            return rows > 0;
        }

        public bool AddList(List<FileData> list)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd;

            StringBuilder sb_D;
            foreach (FileData d in list)
            {
                d.ID = Utils.GetNextID();
                sb_D = new StringBuilder();
                sb_D.Append("insert into " + tableName + "(");
                sb_D.Append("ID,RelationId,FileName,FileType,FilePath,ThumbnailPath,DeletePath,DeleteType,FileStatus,FileSize,IsDelete,RelationTable)");
                sb_D.Append(" values (");
                sb_D.Append("@ID,@RelationId,@FileName,@FileType,@FilePath,@ThumbnailPath,@DeletePath,@DeleteType,@FileStatus,@FileSize,@IsDelete,@RelationTable)");
                SqlParameter[] parameters2 = {
                            new SqlParameter("@ID",  d.ID),
                            new SqlParameter("@RelationId", d.RelationId),
                            new SqlParameter("@FileName",  d.FileName),
                            new SqlParameter("@FileType", d.FileType),
                            new SqlParameter("@FilePath", d.FilePath),
                            new SqlParameter("@ThumbnailPath", d.ThumbnailPath),
                            new SqlParameter("@DeletePath", d.DeletePath),
                            new SqlParameter("@DeleteType", d.DeleteType),
                            new SqlParameter("@FileStatus", d.FileStatus),
                            new SqlParameter("@FileSize", d.FileSize),
                            new SqlParameter("@IsDelete", d.IsDelete),
                            new SqlParameter("@RelationTable", d.RelationTable)
                    };
                cmd = new CommandInfo(sb_D.ToString(), parameters2);
                sqllist.Add(cmd);
            }

            int count = SqlHelper.ExecuteSqlTran(sqllist);

            return count > 0;
        }

    }
}
